package com.hongdezhi.domain;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.hongdezhi.utils.C3P0Utils;


public class PageShowBean {

	private int showpage;//当前所在页
	private int pagesize;//当前页记录数量
	private int countpage;//总记录数所需页数
	private int countrecord;//总记录数
	private List<Notice> userlist=new ArrayList<>();
	public PageShowBean() {
		QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
		String sql="select COUNT(*) from notice_inf;";
		try {
			Number n= runner.query(sql, new ScalarHandler<>());
			countrecord=n.intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public int getShowpage() {
		return showpage;
	}
	public void setShowpage(int showpage) {
		this.showpage = showpage;
	}
	public int getPagesize() {
		return pagesize;
	}
	public void setPagesize(int pagesize) {
		this.pagesize = pagesize;
	}
	public int getCountpage() {
		return countpage;
	}
	public void setCountpage(int countpage) {
		this.countpage = countpage;
	}
	public int getCountrecord() {
		return countrecord;
	}
	public void setCountrecord(int countrecord) {
		this.countrecord = countrecord;
	}
	public List<Notice> getUserlist() {
		
		//计算总记录数/每页记录数=页数
		countpage=(countrecord%pagesize==0)?(countrecord/pagesize):(countrecord/pagesize+1);
		
		if(showpage<1){
			showpage=1;
		}
		if(showpage>countpage){
			showpage=countpage;
		}
		
		/*
		limit子句用于限制查询结果返回的数量。
		用法：【select * from tableName limit i,n 】
		参数：
		tableName : 为数据表；
		i : 为查询结果的索引值（默认从0开始）；
		n : 为查询结果返回的数量
		*/
		
		QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
		String sql="select a.学号 as sno, a.姓名 as name,b.年龄 as age, b.成绩 as score "
				+ "from 用户表 a,信息表 b  where a.学号=b.学号  limit ?,?";
			
		try {
			userlist=  runner.query(sql, new BeanListHandler<>(Notice.class),(showpage-1)*pagesize,pagesize);
			/*
			 * 当前所在页：1，pagesize=20,那么显示数据为0-20条数据
			 * 当前所在页：2，pagesize=20,那么显示数据为20-20条数据
			 */
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userlist;
	}


}
